package cn.nawang.ebeim.client.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.nawang.ebeim.client.bean.UpDownloadFailedBean;

public class DbUpDownloadFailedUtil {
  public static final int STATUS_QUEEING = -1;
  public static final int STATUS_NEW = 0;
  public static final int STATUS_RUNNING = 1;
  public static final int STATUS_FINISHED = 2;
  public static final int STATUS_ERROR = 3;
  
  public static final int FAILED_UPLOAD = 0;
  public static final int FAILED_DOWNLOAD = 1;
  
  private static final String SQL_TABLE_UPDOWNLOAD_FAILED = "create table if not exists updownload_failed("
      + "id integer primary key autoincrement, ds_id varchar(50),signature varchar(255),root varchar(500),path varchar(1000),name varchar(500),nid varchar(50),status integer,type integer,recovery_path varchar(50),time number,uid integer,is_encrypt varchar(1))";
 
  private static final String SQL_INSERT_UPDOWNLOAD_FAILED = "insert into updownload_failed(ds_id,signature,root,path,name,nid,status,type,recovery_path,time,uid,is_encrypt) values(?,?,?, ?,?,?, ?,?, ?,?,?,?)";

  private static final String SQL_SELECT_FAILED =
      "select id,ds_id,signature,root,path,name,nid,status,type,recovery_path,time,uid,is_encrypt"
      + " from updownload_failed where type=? and uid=? order by id asc";
  private static final String SQL_SELECT_FAILED_BY_ID =
      "select id,ds_id,signature,root,path,name,nid,status,type,recovery_path,time,uid,is_encrypt"
      + " from updownload_failed where id=?";
  private static final String SQL_SELECT_FAILED_BY_IDS =
      "select id,ds_id,signature,root,path,name,nid,status,type,recovery_path,time,uid,is_encrypt"
      + " from updownload_failed where id in (IDS)";
  private static final String SQL_SELECT_FAILED_BY_STATUS =
      "select id,ds_id,signature,root,path,name,nid,status,type,recovery_path,time,uid,is_encrypt"
      + " from updownload_failed where status = ?";
  
  
  private static final String SQL_UPDATE = "update updownload_failed set status=? where id=?";
  private static final String SQL_UPDATE_BY_IDS = "update updownload_failed set status=? where id in(IDS)";
  private static final String SQL_UPDATE_NOTRUNNING_BY_IDS = "update updownload_failed set status=? where id in(IDS) and (status=0 or status=3)";
  
  private static final String SQL_DELETE_BY_IDS = "delete from updownload_failed where id in(IDS)";
  private static final String SQL_DELETE_BY_STATUS = "delete from updownload_failed where status =?";
  private static final String SQL_DELETE_LT_TIME = "delete from updownload_failed where time < ?";

  private static Connection connection = null;
  private static boolean isInitTable = false;
  
  public static synchronized void initConnection() throws SQLException {
    if(connection == null){
        String db = FileUtil.getUpDownloadFailedDatabase();
      connection = DriverManager.getConnection("jdbc:sqlite:" + db);
    }
  }

  
  
  public static synchronized void initDatabase() throws SQLException {
    
    if(isInitTable){
        return;
    }
    
    Statement stmt = null;
    try {
        stmt = connection.createStatement();
        stmt.execute(SQL_TABLE_UPDOWNLOAD_FAILED);
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        if(stmt!=null){
            stmt.close();
        }
    }
    isInitTable = true;
 
  }
  
  public static void deleteByIds(String ids) {
    PreparedStatement stmt = null;
    try {
      String sql = SQL_DELETE_BY_IDS;
      sql = sql.replace("IDS", ids);
      stmt = connection.prepareStatement(sql);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  
  public static void updateStatusByIds(int status,String ids) {
    PreparedStatement stmt = null;
    try {
      String sql = SQL_UPDATE_BY_IDS;
      sql = sql.replace("IDS", ids);
      stmt = connection.prepareStatement(sql);
      stmt.setInt(1, status);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  
  public static void updateNotRunningStatusByIds(int status,String ids) {
    PreparedStatement stmt = null;
    try {
      String sql = SQL_UPDATE_NOTRUNNING_BY_IDS;
      sql = sql.replace("IDS", ids);
      stmt = connection.prepareStatement(sql);
      stmt.setInt(1, status);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  
  public static void deleteLtTime(long time) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_DELETE_LT_TIME);
      stmt.setLong(1, time);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  
  public static void deleteByStatus(int status) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_DELETE_BY_STATUS);
      stmt.setInt(1, status);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  
  public static synchronized void updateStatusById(int status,int id) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_UPDATE);
      stmt.setInt(1, status);
      stmt.setInt(2, id);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  public static void addUpDownloadFailed(UpDownloadFailedBean bean) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_INSERT_UPDOWNLOAD_FAILED);
      stmt.setString(1,  bean.getDsId());
      stmt.setString(2,  bean.getSignature());
      stmt.setString(3,  bean.getRoot());
      stmt.setString(4, bean.getPath());
      stmt.setString(5, bean.getName());
      stmt.setString(6, bean.getNid());
      stmt.setInt(7, bean.getStatus());
      stmt.setInt(8, bean.getType());
      stmt.setString(9, bean.getRecoveryPath());
      stmt.setLong(10, bean.getTime());
      stmt.setInt(11, bean.getUid());
      stmt.setString(12, bean.getIsEncrypt());
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  
  public static synchronized UpDownloadFailedBean getUpDownloadFailedBean(int id) {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    UpDownloadFailedBean bean = null;
    try {
      stmt = connection.prepareStatement(SQL_SELECT_FAILED_BY_ID);
      stmt.setInt(1, id);
      rs = stmt.executeQuery();
      while (rs.next()) {
        bean = new UpDownloadFailedBean();
        int _id= rs.getInt("id");
        String dsId = rs.getString("ds_id");
        String signature = rs.getString("signature");
        String root = rs.getString("root");
        String path = rs.getString("path");
        String name = rs.getString("name");
        String nid = rs.getString("nid");
        int status = rs.getInt("status");
        int _type = rs.getInt("type");
        String recoveryPath = rs.getString("recovery_path");
        long time = rs.getLong("time");
        int uid = rs.getInt("uid");
        String isEncrypt = rs.getString("is_encrypt");
        bean.setId(_id);
        bean.setDsId(dsId);
        bean.setSignature(signature);
        bean.setRoot(root);
        bean.setPath(path);
        bean.setName(name);
        bean.setNid(nid);
        bean.setStatus(status);
        bean.setType(_type);
        bean.setRecoveryPath(recoveryPath);
        bean.setTime(time);
        bean.setUid(uid);
        bean.setIsEncrypt(isEncrypt);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }
    return bean;
  }
  
  public static synchronized List<UpDownloadFailedBean> getUpDownloadFaileds(int type,int uid) {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    UpDownloadFailedBean bean = null;
    List<UpDownloadFailedBean> list = new ArrayList<UpDownloadFailedBean>();
    try {
      stmt = connection.prepareStatement(SQL_SELECT_FAILED);
      stmt.setInt(1, type);
      stmt.setInt(2, uid);
      rs = stmt.executeQuery();
      while (rs.next()) {
        bean = new UpDownloadFailedBean();
        int id= rs.getInt("id");
        String dsId = rs.getString("ds_id");
        String signature = rs.getString("signature");
        String root = rs.getString("root");
        String path = rs.getString("path");
        String name = rs.getString("name");
        String nid = rs.getString("nid");
        int status = rs.getInt("status");
        int _type = rs.getInt("type");
        String recoveryPath = rs.getString("recovery_path");
        long time = rs.getLong("time");
        int _uid = rs.getInt("uid");
        String isEncrypt = rs.getString("is_encrypt");
        bean.setId(id);
        bean.setDsId(dsId);
        bean.setSignature(signature);
        bean.setRoot(root);
        bean.setPath(path);
        bean.setName(name);
        bean.setNid(nid);
        bean.setStatus(status);
        bean.setType(_type);
        bean.setRecoveryPath(recoveryPath);
        bean.setTime(time);
        bean.setUid(_uid);
        bean.setIsEncrypt(isEncrypt);
        list.add(bean);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }
    return list;
  }
  
  public static synchronized List<UpDownloadFailedBean> upDownloadFailedsTryagain(String ids) {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    UpDownloadFailedBean bean = null;
    List<UpDownloadFailedBean> list = new ArrayList<UpDownloadFailedBean>();
    try {
      String sql = SQL_SELECT_FAILED_BY_IDS;
      sql = sql.replace("IDS", ids);
      stmt = connection.prepareStatement(sql);
      rs = stmt.executeQuery();
      while (rs.next()) {
        bean = new UpDownloadFailedBean();
        int id= rs.getInt("id");
        String dsId = rs.getString("ds_id");
        String signature = rs.getString("signature");
        String root = rs.getString("root");
        String path = rs.getString("path");
        String name = rs.getString("name");
        String nid = rs.getString("nid");
        int status = rs.getInt("status");
        int _type = rs.getInt("type");
        String recoveryPath = rs.getString("recovery_path");
        long time = rs.getLong("time");
        int _uid = rs.getInt("uid");
        String isEncrypt = rs.getString("is_encrypt");
        bean.setId(id);
        bean.setDsId(dsId);
        bean.setSignature(signature);
        bean.setRoot(root);
        bean.setPath(path);
        bean.setName(name);
        bean.setNid(nid);
        bean.setStatus(status);
        bean.setType(_type);
        bean.setRecoveryPath(recoveryPath);
        bean.setTime(time);
        bean.setUid(_uid);
        bean.setIsEncrypt(isEncrypt);
        list.add(bean);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }
    return list;
  }
  
  public static synchronized List<UpDownloadFailedBean> getUpDownloadFailedsByStatus(int status) {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    UpDownloadFailedBean bean = null;
    List<UpDownloadFailedBean> list = new ArrayList<UpDownloadFailedBean>();
    try {
      stmt = connection.prepareStatement(SQL_SELECT_FAILED_BY_STATUS);
      stmt.setInt(1, status);
      rs = stmt.executeQuery();
      while (rs.next()) {
        bean = new UpDownloadFailedBean();
        int id= rs.getInt("id");
        String dsId = rs.getString("ds_id");
        String signature = rs.getString("signature");
        String root = rs.getString("root");
        String path = rs.getString("path");
        String name = rs.getString("name");
        String nid = rs.getString("nid");
        int _status = rs.getInt("status");
        int _type = rs.getInt("type");
        String recoveryPath = rs.getString("recovery_path");
        long time = rs.getLong("time");
        int _uid = rs.getInt("uid");
        String isEncrypt = rs.getString("is_encrypt");
        bean.setId(id);
        bean.setDsId(dsId);
        bean.setSignature(signature);
        bean.setRoot(root);
        bean.setPath(path);
        bean.setName(name);
        bean.setNid(nid);
        bean.setStatus(_status);
        bean.setType(_type);
        bean.setRecoveryPath(recoveryPath);
        bean.setTime(time);
        bean.setUid(_uid);
        bean.setIsEncrypt(isEncrypt);
        list.add(bean);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }
    return list;
  }
  
  public static void close(PreparedStatement stmt, ResultSet rs) {
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    if (stmt != null) {
      try {
        stmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public static void close(PreparedStatement stmt) {
    if (stmt != null) {
      try {
        stmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public static void close() {

    if (connection != null) {
      try {
        connection.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }


}
